package com.campanyes.entitites;

import java.sql.Statement;

import org.apache.log4j.Logger;


public class ConsultasBBDD {

	Statement s;
	
	private static final Logger logger = Logger.getLogger(ConsultasBBDD.class);
	
	
	public ConsultasBBDD(Statement s){
		this.s = s;
	}
	
	public int insertarOpositor(String regio, String anyo, Opositor opositor){
		int res=0;				
	
		try{					
			String query = 
            "INSERT INTO "+regio+"_"+anyo+" (clave, DNI, nom, cognom1, cognom2, merits, fisiques, nfinal) VALUES('"+opositor.getClave()+"', '"+opositor.getDNI()+"', '"+opositor.getNom()+"', '"+ opositor.getCognom1()+"', '"+ opositor.getCognom2()+"', '"+opositor.getMerits()+"', '"+ opositor.getFisiques()+"', '"+opositor.getNfinal()+"')";
            			
            //logger.info(opositor);
        	//logger.info(query);        	    	
        	//logger.info("");
        	
        	
			res = s.executeUpdate(query);
		}catch(Exception e){			
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;
	}
	
	
	
	
	public int insertarOpositor2012(Opositor opositor){
		int res=0;				
	
		try{					
			String query = 
            " INSERT INTO FORESTALS_2012 (clave, DNI, nom, cognom1, cognom2, regio) " +
            " VALUES('"+opositor.getClave()+"', " +
            		"'"+opositor.getDNI()+"', '"+opositor.getNom()+"', '"+ opositor.getCognom1()+"', '"+ opositor.getCognom2()+"', '"+opositor.getRegio()+"')";
            			
            //logger.info(opositor);
        	//logger.info(query);        	    	
        	//logger.info("");
        	
        	
			res = s.executeUpdate(query);
		}catch(Exception e){
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;
	}

	public int insertarEPAF2011(EPAF epaf){
		int res=0;				
		
		try{					
			String query = 
            " INSERT INTO EPAFS_2011 (clave, DNI, nom, cognom1, cognom2, puntacio_proces, serveis_prestats, puntuacio_total) " +
            " VALUES('"+epaf.getClave()+"', " +
            		"'"+epaf.getDNI()+"', '"+epaf.getNom()+"', '"+ epaf.getCognom1()+"', '"+ epaf.getCognom2()+"', '"+epaf.getPuntacio_proces()+"', '"+epaf.getServeis_prestats()+"', '"+epaf.getPuntuacio_total()+"')";        
        	
			res = s.executeUpdate(query);
		}catch(Exception e){
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;		
	}
	
	public int insertarAOF2012(Forestal2012 aof2012){
		int res=0;				
		
		try{					
			String query = 
            " INSERT INTO AOFS_2012 (clave, DNI, nom, cognom1, cognom2, nota, regio) " +
            " VALUES('"+aof2012.getClave()+"', " +
            		"'"+aof2012.getDNI()+"', '"+aof2012.getNom()+"', '"+ aof2012.getCognom1()+"', '"+ aof2012.getCognom2()+"', '"+aof2012.getNota()+"', '"+aof2012.getRegio()+"')";            
        	
			res = s.executeUpdate(query);
		}catch(Exception e){
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;		
	}
	
	public int insertarEPAF2010(EPAF epaf){
		int res=0;				
		
		try{					
			String query = 
            " INSERT INTO EPAFS_2010 (clave, DNI, nom, cognom1, cognom2) " +
            " VALUES('"+epaf.getClave()+"', " +
            		"'"+epaf.getDNI()+"', '"+epaf.getNom()+"', '"+ epaf.getCognom1()+"', '"+ epaf.getCognom2()+"')";        
        	
			res = s.executeUpdate(query);
		}catch(Exception e){
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;		
	}
	
	public int insertarEPAF2011_NO_VAN_FER_EPAF(EPAF epaf){
		int res=0;				
		
		try{					
			String query = 
            " INSERT INTO EPAFS_2011_NO_VAN_FER_EPAF (clave, DNI, nom, cognom1, cognom2) " +
            " VALUES('"+epaf.getClave()+"', " +
            		"'"+epaf.getDNI()+"', '"+epaf.getNom()+"', '"+ epaf.getCognom1()+"', '"+ epaf.getCognom2()+"')";        
        	
			res = s.executeUpdate(query);
		}catch(Exception e){
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;		
	}
	
	
	
	public int deleteAllOpositor2012(){
		int res=0;
		
		String query = "DELETE FROM FORESTALS_2012";
		try{
			res = s.executeUpdate(query);
		}catch(com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException e){
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}catch(Exception e){		
			logger.error(e.getMessage());
			logger.error(e);
			e.printStackTrace();
			System.exit(0);
		}
		return res;
	}
		
}
